package com.ssy.lingxi.report.dao.pay;

import com.ssy.lingxi.report.model.bo.tally.statistics.CreditStatisticsTallyBO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 授信申请
 * @author 伍将
 * @date 2020/11/14
 * @version 2.0.0
 */
@Service
public class CreditDAO {

    @Autowired
    @Qualifier("payJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取授信统计数量
     * @return
     */
    public List<CreditStatisticsTallyBO> getCreditApplyStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" parent_member_id member_id,parent_member_role_id role_id,")
                .append(" sum(case when superior_inner_status = 1  then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when superior_inner_status = 3  then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when superior_inner_status = 5  then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when superior_inner_status = 7  then 1 else 0 end) toBeValifyStep3Count,")
                .append(" sum(case when superior_inner_status in(2,4,6,8,9)  then 1 else 0 end) toBeConfirmCount")
                .append(" FROM ")
                .append(" pay_credit_apply ")
                .append(" where is_current=1")  //当前申请
                .append(" and outer_status=2")
                .append(" and is_delete=0")     //未删除
                .append(" group by parent_member_id,parent_member_role_id");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CreditStatisticsTallyBO.class));
    }
}
